import mysql.connector as mc
from mysql.connector import errorcode
import numpy as np
import datetime
import time

config = {
  'user': 'root',
  'password': 'fxqs123',
  'host': '127.0.0.1',
  'database': 'selectdatas',
  'raise_on_warnings': True,
}

class Mysql():
    def __init__(self,name):
        try:
            print('开始连接数据库：',end='',flush=True)
            self.cnx = mc.connect(**config)
        except mc.Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                print("用户名密码错误")
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                print("数据库不存在")
            else:
                print(err)
        else:
            print('线程{}数据库{}已连接'.format(name,config['database']))
            self.cursor=self.cnx.cursor()

    def Creat_table1v1(self):
        name='1v1_test_case'
        table_form='CREATE TABLE `'+name+'` ('\
                      '`ID` INT PRIMARY KEY AUTO_INCREMENT,'\
                      '`U_X` FLOAT NOT NULL,'\
                      '`U_Y` FLOAT NOT NULL,'\
                      '`U_Z` FLOAT NOT NULL,'\
                      '`A_X` FLOAT NOT NULL,'\
                      '`A_Y` FLOAT NOT NULL,'\
                      '`A_Z` FLOAT NOT NULL,'\
                      '`Decision_Num` Int NULL,'\
                      '`H_expectation` FLOAT NOT NULL'\
                      ');'

        try:
            print("创建表格 {}:".format(name),end='')
            self.cursor.execute(table_form)
        except mc.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("该表已存在")
            else:
                print(err.msg)
        else:
            print("OK")
        return name

    def Creat_table2v2(self):
        name='2V2_test_case'
        table_form='CREATE TABLE `'+name+'` ('\
                      '`ID` INT PRIMARY KEY AUTO_INCREMENT,'\
                      '`U1_X` FLOAT NOT NULL,'\
                      '`U1_Y` FLOAT NOT NULL,'\
                      '`U1_Z` FLOAT NOT NULL,' \
                      '`U2_X` FLOAT NOT NULL,' \
                      '`U2_Y` FLOAT NOT NULL,' \
                      '`U2_Z` FLOAT NOT NULL,' \
                      '`A1_X` FLOAT NOT NULL,' \
                      '`A1_Y` FLOAT NOT NULL,' \
                      '`A1_Z` FLOAT NOT NULL,' \
                      '`A2_X` FLOAT NOT NULL,' \
                      '`A2_Y` FLOAT NOT NULL,' \
                      '`A2_Z` FLOAT NOT NULL,' \
                      '`Decision_Num` Int NULL,' \
                      '`H_expectation` FLOAT NOT NULL' \
                      ');'

        try:
            print("创建表格 {}:".format(name),end='')
            self.cursor.execute(table_form)
        except mc.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("该表已存在")
            else:
                print(err.msg)
        else:
            print("OK")
        return name

    def Insert_record(self,name,data):
        add_recordtable = ("INSERT INTO `"+name+"` "
                        "(U_X,U_Y,U_Z,A_X,A_Y,A_Z,Trajectory_Num,H_expectation)"
                        "VALUES(%s, %s, %s, %s, %s,%s, %s, %s)")
        if type(data)==tuple:
            try:
                print('添加记录：',end='')
                self.cursor.execute(add_recordtable,data)
            except mc.Error as err:
                print(err.msg)
            else:
                self.cnx.commit()
                print('成功')

        if type(data)==list:
            try:
                print('添加记录：',end='')
                self.cursor.executemany(add_recordtable,data)
            except mc.Error as err:
                print(err.msg)
            else:
                self.cnx.commit()
                print('成功')

    def Insert_record2(self,name,data):
        add_recordtable = ("INSERT INTO `"+name+"` "
                        "(U1_X,U1_Y,U1_Z,U2_X,U2_Y,U2_Z,"
                        "A1_X,A1_Y,A1_Z,A2_X,A2_Y,A2_Z,Decision_Num,H_expectation)"
                        "VALUES(%s, %s, %s, %s, %s,%s, %s, %s,%s, %s, %s, %s, %s,%s)")
        if type(data)==tuple:
            try:
                print('添加记录：',end='')
                self.cursor.execute(add_recordtable,data)
            except mc.Error as err:
                print(err.msg)
            else:
                self.cnx.commit()
                print('成功')

        if type(data)==list:
            try:
                print('添加记录：',end='')
                self.cursor.executemany(add_recordtable,data)
            except mc.Error as err:
                print(err.msg)
            else:
                self.cnx.commit()
                print('成功')

    def Close(self):
        self.cursor.close()
        self.cnx.close()

    def get_conn(self):
        return self.cnx


######################################测试用例#################################################
if __name__=='__main__':
    conn=Mysql('test')
    name=conn.Creat_table1v1()

    conn.Close()


